Setting up a Microsoft Azure SQL Managed Instance for CDC
The following section explains how to set up a Microsoft Azure SQL Managed Instance for CDC.
When creating the Microsoft Azure SQL Managed Instance, full backup must be enabled; otherwise, Replicate will not be able to capture changes.
To set up a Microsoft Azure SQL Managed Instance for CDC:
-
Log in as the master user in the DB instance and set up the database for MS-CDC as follows:
exec source_db_name.dbo.sp_cdc_enable_db
where source_db_name is the name of the source database.
-
To enable MS-CDC, run the following for each of the intended source tables:
exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
exec sys.sp_cdc_add_job @job_type = 'capture'
exec sys.sp_cdc_add_job @job_type = 'cleanup'
GO
where schema_name and table_name are the names of the source schema and the source table respectively.
-
Set the retention period for the changes to be available on the source by changing the @pollinginterval of the 'capture' job to the maximum possible value.
Limitations
- CDC is supported with the transaction log only, with no option to work with backup.
- Events may be lost if they are moved to backup or truncated.